﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Web.UI;

namespace BLL
{
    public class Sys_BankBLL : BaseBLL
    {
        public SYS_BANK Find(decimal id)
        {
            var sql = "SELECT * FROM SYS_BANK WHERE N_ID = {0}";
            sql = string.Format(sql, id);
            return ConvertToEntity(RowSingeleOfNull(sql));
        }

        public List<SYS_BANK> GetEntitys(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var dt = GetData(whereStr, orderbyStr, skip, take, out count);
            var entitys = new List<SYS_BANK>();
            foreach (DataRow item in dt.Rows)
            {
                entitys.Add(ConvertToEntity(item));
            }
            return entitys;
        }

        public DataTable GetData(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine("SELECT * FROM SYS_BANK");
            if (whereStr != null)
            {
                sqlBuilder.AppendLine(" WHERE " + whereStr);
            }
            if (orderbyStr != null)
            {
                sqlBuilder.AppendLine(" ORDER BY " + orderbyStr);
            }
            count = SqlCount(sqlBuilder.ToString());
            return PageQuery(sqlBuilder.ToString(), skip, take);
        }

        /// <summary>
        /// 查询银行信息
        /// </summary>
        /// <returns></returns>
        public DataTable SelBank()
        {
            string sql = @"select * from sys_bank t";

            DataTable dt = Query(sql);
            return dt;
        }

        /// <summary>
        /// 查询银行账号
        /// </summary>
        /// <returns></returns>
        public DataTable SelC_ACCNO2()
        {
            string sql = @"SELECT * FROM YX_CUSTOMER_BANK";
            DataTable dt = Query(sql);
            return dt;
        }

        public List<SYS_BANK> GetEntitys2(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var dt = GetData2(whereStr, orderbyStr, skip, take, out count);
            var entitys = new List<SYS_BANK>();
            foreach (DataRow item in dt.Rows)
            {
                entitys.Add(ConvertToEntity(item));
            }
            return entitys;
        }

        public DataTable GetData2(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine(@"SELECT DISTINCT T1.N_ID AS N_ID, T1.C_NAME AS C_NAME
  FROM SYS_BANK T1, YX_CUSTOMER_BANK T2
 WHERE T1.N_ID = T2.N_BANK_ID");
            if (whereStr != null)
            {
                sqlBuilder.AppendLine(" AND " + whereStr);
            }
            if (orderbyStr != null)
            {
                sqlBuilder.AppendLine(" ORDER BY " + orderbyStr);
            }
            count = SqlCount(sqlBuilder.ToString());
            return PageQuery(sqlBuilder.ToString(), skip, take);
        }

        private SYS_BANK ConvertToEntity(DataRow row)
        {
            if (row == null)
            {
                return null;
            }
            var entity = new SYS_BANK
            {
                N_ID = Convert.ToDecimal(row["N_ID"]),
                C_NAME = Convert.ToString(row["C_NAME"])
            };
            return entity;
        }

        public bool Insert(SYS_BANK entity, string operaname)
        {
            entity.N_ID = GetSequence("SEQ_N_BANK_ID");
            var sql = @"INSERT INTO SYS_BANK(
	N_ID,
	C_NAME
)
VALUES(
	{0},
	'{1}'
)";
            sql = string.Format(sql, entity.N_ID, entity.C_NAME);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        public bool Update(SYS_BANK entity, string operaname)
        {
            var sql = @"UPDATE SYS_BANK
SET
	C_NAME = '{1}'
WHERE
	N_ID = {0}";
            sql = string.Format(sql, entity.N_ID, entity.C_NAME);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        /// <summary>
        /// 对银行的模糊查询
        /// </summary>
        /// <returns></returns>
        public string Likeb(string bname)
        {
            string sql = @"SELECT C_NAME FROM SYS_BANK T WHERE C_NAME LIKE '%" + bname + "%'";
            string strName = "";
            DataTable dt = Query(sql);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Columns[i].ColumnName = strName;
                //strName = dt.Rows[i]["C_NAME"].ToString();
            }
            return strName;
        }

        /// <summary>
        /// 弹框
        /// </summary>
        /// <param name="info"></param>
        /// <param name="p"></param>
        public static void Alert(string info, Page p)
        {
            string script = "<script>alert('" + info + "')</script>";
            p.ClientScript.RegisterStartupScript(p.GetType(), "", script);
        }
    }
}